﻿using KSSModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.Script.Serialization;

namespace KSSOnline.Controller
{
    public static class UserHelper
    {
        public static List<tblUser> ValidateUser(string username, string password)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                try
                {
                    var user = (from users in dataContext.tblUsers
                                where users.UserName == username &&
                                users.Password == password
                                select users).ToList();

                    return user;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return null;
                }
            }
        }
        public static List<tblUser> ValidateUserAd(string username)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                List<tblUser> user = new List<tblUser>();
                try
                {
                    user = (from users in dataContext.tblUsers
                                where users.Email.ToLower().Remove(users.Email.IndexOf("@")).Contains(username.ToLower())
                                select users).ToList();

                    if (!user.Any())
                    {
                        var userHr =
                            dataContext.tblEDMSHRs.Where(
                                u => u.memail.ToLower().Remove(u.memail.IndexOf("@")).Contains(username.ToLower()));
                        if (userHr.Any())
                        {
                            var uHr = userHr.ToList();
                            var newuser = new tblUser
                                              {
                                                  UserName = uHr[0].mstaffno.Replace("-", ""),
                                                  FirstName = uHr[0].mnameTh,
                                                  Lastname = uHr[0].surnameTh,
                                                  Password = "default12356",
                                                  CreateDate = DateTime.Now,
                                                  IsDisable = false,
                                                  Email = uHr[0].memail
                                              };
                            user.Add(newuser);
                            dataContext.tblUsers.InsertOnSubmit(newuser);
                            dataContext.tblUserRoles.InsertOnSubmit(new tblUserRole { RoleName = "user", UserName = uHr[0].mstaffno });
                            dataContext.SubmitChanges();
                            
                        }
                    }

                    return user;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return user;
                }
            }
        }

        public static string resetPassword(string newPassword)
        {
            var user = System.Web.HttpContext.Current.User.Identity.Name;
            var serializer = new JavaScriptSerializer();
            if (user != null )
            {
                using (var dataContext = new KSSDBDataContext())
                {
                    try
                    {
                        var us = dataContext.tblUsers.FirstOrDefault(u => u.UserName == user);
                        if (us != null)
                        {
                            us.Password = newPassword;
                            dataContext.SubmitChanges();
                            KSSMaster.AddLog(new tblLog()
                            {
                                details =
                                    "reset Password " + user,
                                username = "system",
                                logType = 0
                            });
                            return serializer.Serialize(new Result(true, ""));
                        }
                        
                    }
                    catch (Exception ex)
                    {
                        return serializer.Serialize(new Result(false, ex.Message)); ;
                    }
                    
                }
            }
            return serializer.Serialize(new Result(false, "ไม่มีข้อมูลพนักงาน สำหรับการ reset password กรุณาติดต่อผู้ดูแลระบบ"));
        }

        public static string addUser(tblUser user)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    if (dataContext.tblUsers.Any(u => u.UserName == user.UserName && u.Password != null))
                    {

                        //var us = dataContext.tblUsers.Single(u => u.UserName == user.UserName);
                        //us.Password = user.Password;
                        //dataContext.SubmitChanges();
                        //KSSMaster.AddLog(new tblLog()
                        //{
                        //    details =
                        //        "แก้ไข user " + user.UserName,
                        //    username = "system",
                        //    logType = 0
                        //});
                        return serializer.Serialize(new Result(false, "รหัสพนักงานนี้ได้ทำการลงทะเบียนแล้ว หากท่านลืมรหัสผ่านกรุณาติดต่อ QM แต่ละ Location"));
                    }
                    else
                    {
                        var us = dataContext.tblUsers.FirstOrDefault(u => u.UserName == user.UserName);
                        if (us != null)
                        {
                            us.Password = user.Password;
                            dataContext.SubmitChanges();
                            KSSMaster.AddLog(new tblLog()
                            {
                                details =
                                    "แก้ไข user " + user.UserName,
                                username = "system",
                                logType = 0
                            });

                        }
                        else
                        {
                            dataContext.tblUsers.InsertOnSubmit(user);
                            dataContext.SubmitChanges();
                            KSSMaster.AddLog(new tblLog()
                            {
                                details =
                                    "เพิ่ม user " + user.UserName,
                                username = "system",
                                logType = 0
                            });
                        }
                    }
                    
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        username = "system",
                        logType = 1
                    });
                    return serializer.Serialize(new Result(false, ex.Message));
                }

                return serializer.Serialize(new Result(true));
            }
        }
        public static string addHrUser(List<tblEDMSHR> hruser)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    foreach (var hru in hruser)
                    {
                        if (dataContext.tblUsers.Any(u => u.UserName == hru.mstaffno))
                        {
                            return serializer.Serialize(new Result(false, "รหัสพนักงาน "+ hru.mstaffno + " มีในระบบฐานข้อมูลแล้ว."));
                        }
                        dataContext.tblEDMSHRs.InsertOnSubmit(hru);
                        KSSMaster.AddLog(new tblLog()
                        {
                            details =
                                "เพิ่ม HR user " + hru.mstaffno,
                            username = "system",
                            logType = 0
                        });
                    }
                    dataContext.SubmitChanges();
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        username = "system",
                        logType = 1
                    });
                    return serializer.Serialize(new Result(false, ex.Message));
                }

                return serializer.Serialize(new Result(true));
            }
        }
        public static string addUserRole(tblUserRole user)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    dataContext.tblUserRoles.InsertOnSubmit(user);
                    dataContext.SubmitChanges();
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        username = "system",
                        logType = 1
                    });
                    return serializer.Serialize(new Result(false, ex.Message));
                }

                return serializer.Serialize(new Result(true));
            }
        }
        public static bool deleteHrUser(string hrUserID)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                try
                {
                    var deleteProp = "DELETE FROM tblEDMSHR WHERE mtypeid = '1' and mstaffno ='" + hrUserID + "'";
                    dataContext.ExecuteCommand(deleteProp);
                    dataContext.SubmitChanges();
                    return true;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return false;
                }
            }
        }
        public static List<tblEDMSHR> getNonEmp()
        {
            using (var dataContext = new KSSDBDataContext())
            {
                try
                {
                    var emp = (from users in dataContext.tblEDMSHRs
                               where users.mtypeid == 1
                               select users).ToList();

                    return emp;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return null;
                }
            }
        }
        public static List<tblEDMSHR> getAllEMP()
        {
            using (var dataContext = new KSSDBDataContext())
            {
                try
                {
                    var emp = (from users in dataContext.tblEDMSHRs
                               select users).ToList();

                    return emp;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return null;
                }
            }
        }

        public static string updateHrUser(List<tblEDMSHR> newuser)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    foreach (var u in newuser)
                    {
                        tblEDMSHR olduser =
                        dataContext.tblEDMSHRs.Single(o => o.mstaffno == u.mstaffno);
                        if (olduser != null)
                        {
                            olduser = u;
                        }
                        KSSMaster.AddLog(new tblLog()
                        {
                            details =
                                "แก้ไข HR user " + u.mstaffno,
                            username = "system",
                            logType = 0
                        });
                    }
                    
                    dataContext.SubmitChanges();
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return serializer.Serialize(new Result(false, ex.Message));
                }
                return serializer.Serialize(new Result(true));

            }
        }
        public static string updateUserRole(tblUserRole olduserrole, tblUserRole newuserrole)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    List<tblUserRole> userRole =
                        dataContext.tblUserRoles.Where(
                            o => o.RoleName == olduserrole.RoleName && o.UserName == olduserrole.UserName).ToList();
                    if (userRole.Any())
                    {
                        if (userRole[0].RoleName != null && userRole[0].RoleName != newuserrole.RoleName)
                        {
                            userRole[0].RoleName = newuserrole.RoleName;
                        }
                    }
                    else
                    {
                        dataContext.tblUserRoles.InsertOnSubmit(newuserrole);
                    }
                    dataContext.SubmitChanges();
                }
                catch (Exception ex)
                {

                    KSSMaster.AddLog(new tblLog()
                                         {
                                             details =
                                                 ex.Message,
                                             logType = 1
                                         });
                    return serializer.Serialize(new Result(false, ex.Message));
                }
                return serializer.Serialize(new Result(true));

            }
        }

        public static string getAllRole()
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var role = (from roles in dataContext.tblRoles
                                select new
                                           {
                                               roles.RoleID,
                                               roles.RoleName
                                           }).ToList();

                    return serializer.Serialize(role);
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return null;
                }
            }

        }

        public static string getEmailTQMByLocation(string docOwnerId)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                string emailTo = "";
                try
                {
                    var qmlocation =
                        dataContext.tblEDMSHRs.FirstOrDefault(u => u.mstaffno.Equals(docOwnerId)).mlocation;
                    var tqms =
                        from user in dataContext.tblUsers
                        join role in dataContext.tblUserRoles 
                        on user.UserName equals role.UserName
                        join edms in dataContext.tblEDMSHRs
                        on user.UserName equals edms.mstaffno
                        where role.RoleName.ToLower() =="tqm" && edms.mlocation == qmlocation
                        select user.Email;

                    foreach (var tqm in tqms)
                    {
                        emailTo += tqm + ";";
                    }
                    return emailTo;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return "";
                }
            }
        }

        public static string getUserwithRole(string keyword, string searchtype)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var userrole = from user in dataContext.tblUsers
                                join role in dataContext.tblUserRoles
                                    on user.UserName equals role.UserName into userRole
                                from k in userRole.DefaultIfEmpty()
                                select new
                                           {
                                               user.UserName,
                                               user.FirstName,
                                               user.Lastname,
                                               user.Password,
                                               user.CreateDate,
                                               k.RoleName

                                           };
                    //var userrole = dataContext.tblUsers.Join(dataContext.tblUserRoles, user => user.UserName,
                    //                                          Userrole => Userrole.UserName,
                    //                                          (user, Userrole) => new { user, Userrole }).Join(
                    //                                              dataContext.tblRoles, @t => @t.Userrole.RoleName,
                    //                                              role => role.RoleName, (@t, role) => new
                    //                                                                                       {
                    //                                                                                           @t.user.UserName,
                    //                                                                                           @t.user.FirstName,
                    //                                                                                           @t.user.Lastname,
                    //                                                                                           @t.user.Password,
                    //                                                                                           @t.user.CreateDate,
                    //                                                                                           @t.Userrole.RoleName
                    //                                                                                       });

                    if (!string.IsNullOrEmpty(keyword) && !string.IsNullOrEmpty(searchtype))
                    {
                        switch (searchtype)
                        {
                            case "searcMname":
                                userrole = userrole.Where(u => u.FirstName.Contains(keyword) || u.Lastname.Contains(keyword));
                                break;
                            case "searcMid":
                                userrole = userrole.Where(u => u.UserName.Contains(keyword));
                                break;
                        }
                    }
                    userrole = userrole.Distinct();

                    return serializer.Serialize(userrole);
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return null;
                }
            }
        }
        public static List<tblEDMSHR> getEMP(string empID)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                try
                {
                    var emp = (from users in dataContext.tblEDMSHRs
                               where users.mstaffno.Equals(empID)
                               select users).ToList();

                    return emp;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return null;
                }
            }
        }
        public static tblUser GetUserbyUserName(string username)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                try
                {
                    var user = (from users in dataContext.tblUsers
                                where users.UserName == username
                                select users).First();

                    return user;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return null;
                }
            }
        }

        public static decimal? GetUserCanCash(string username)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                try
                {
                    decimal? canCash = (from doc in dataContext.tblDocs
                                where doc.createdBy == username && doc.status == 30
                                   select doc).Sum(d=>d.cash);

                    return canCash ?? 0;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return null;
                }
            }
        }

        public static string GetUserByRole(string role)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var user = (from users in dataContext.tblUsers
                                //join userRole in dataContext.tblUserRoles
                              // on users.UserName equals userRole.UserName
                               // where userRole.RoleName == role
                                select new
                                {
                                    users.UserName,
                                    FirstName = users.FirstName + " " + users.Lastname
                                });
                    var aView = serializer.Serialize(user);
                    return aView;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return null;
                }
            }
        }
        public static string GetAllUserExceptSelf(string username)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var user = (from users in dataContext.tblUsers
                                where users.UserName != username && users.FirstName != ""
                                orderby users.FirstName
                                select new
                                {
                                    users.UserName,
                                    FirstName = users.FirstName + " " + users.Lastname
                                });
                    var aView = serializer.Serialize(user);
                    return aView;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return null;
                }
            }
        }
        public static tblDoc GetLastSelectedApprover(string username)
        {
            using (var dc = new KSSDBDataContext())
            {
                try
                {
                    var k =
                    dc.tblDocs.Where(i => i.createdBy.Equals(username)).OrderByDescending(o => o.createdDate).First();
                    return k;
                }
                catch (Exception)
                {

                    return new tblDoc();
                }
                
            }
        }
        public static List<tblRole> GetRolebyUserName(string username)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                try
                {
                    var Role = (from Roles in dataContext.tblRoles
                                join userRole in dataContext.tblUserRoles
                                on Roles.RoleName equals userRole.RoleName
                                where userRole.UserName == username
                                select Roles).ToList();

                    return Role;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return null;
                }

            }
        }
        public static string GetAllUser()
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var user = from users in dataContext.tblUsers
                               select new
                               {
                                   UserName = users.UserName,
                                   FirstName = users.FirstName + " " + users.Lastname
                               };

                    var aView = serializer.Serialize(user);
                    return aView;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return null;
                }
            }
        }
    }
}
